﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Threading;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;

namespace Systemathics.FrameWork
{
    public sealed class ToExcel : IDisposable
    {
        private Application app;
        private Workbooks workbooks;
        private Workbook workbook;
        private Worksheet workSheets;
        private Range range;
        private readonly String FileName;
        private int CurrentWorkSheet;
        public String WorkSheetName { get; set; }

        public ToExcel(String fileName)
        {
            Thread.CurrentThread.CurrentCulture = GlobalVariables.SystemathicsCulture();
            app = new Application();
            workbooks = app.Workbooks;
            workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            FileName = fileName;
            CurrentWorkSheet = 0;
            WorkSheetName = String.Format("{0:ddMMyyyy hh mm}", DateTime.Now);
        }

        public void Add(QuoteSeries qs)
        {
            if(qs.Count > 0)
            {
                var rows = 0;
                var o = new Object[qs.Count, 5];
                foreach (var quote in qs)
                {
                    o[rows, 0] = quote.Time.ToOADate();
                    o[rows, 1] = quote.BidSize;
                    o[rows, 2] = quote.Bid;
                    o[rows, 3] = quote.Ask;
                    o[rows, 4] = quote.AskSize;
                    rows++;
                }
                CurrentWorkSheet++;
                workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
                workSheets.Name = WorkSheetName;
                range = workSheets.UsedRange;
                range = range.get_Resize(qs.Count, 5);
                range.Value2 = o;

            }
        }
        public void Add(BarSeries bs)
        {
            if(bs.Count > 0)
            {
                var rows = 0;
                var o = new Object[bs.Count, 6];
                foreach (var bar in bs)
                {
                    o[rows, 0] = bar.BeginTime.ToOADate();
                    o[rows, 1] = bar.Open;
                    o[rows, 2] = bar.High;
                    o[rows, 3] = bar.Low;
                    o[rows, 4] = bar.Close;
                    o[rows, 5] = bar.Volume;
                    rows++;
                }
                CurrentWorkSheet++;
                workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
                workSheets.Name = WorkSheetName;
                range = workSheets.UsedRange;
                range = range.get_Resize(bs.Count, 6);
                range.Value2 = o;
   
            }
        }
        public void Add(TradeSeries ts)
        {
            if(ts.Count > 0)
            {
                var rows = 0;
                var o = new Object[ts.Count, 3];
                foreach (var trade in ts)
                {
                    o[rows, 0] = trade.Time.ToOADate();
                    o[rows, 1] = trade.Price;
                    o[rows, 2] = trade.Size;
                    rows++;
                }
                CurrentWorkSheet++;
                workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
                workSheets.Name = WorkSheetName;
                range = workSheets.UsedRange;
                range = range.get_Resize(ts.Count, 3);
                range.Value2 = o;
                
            }
        }
        public void Add(TimeSeries ts)
        {
            if(ts.Count() > 0)
            {
                var rows = 0;
                var o = new Object[ts.TimeSerie.Count, 2];
                foreach (KeyValuePair<DateTime, Decimal> kvp in ts.TimeSerie)
                {
                    o[rows, 0] = kvp.Key.ToOADate();
                    o[rows, 1] = kvp.Value;
                    rows++;
                }
                CurrentWorkSheet++;
                workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
                workSheets.Name = WorkSheetName;
                range = workSheets.UsedRange; 
                range = range.get_Resize(ts.TimeSerie.Count, 2);
                range.Value2 = o;

            }
        }
        public void Add(DataSet dataSet)
        {
            var o = new Object[dataSet.Tables[0].Rows.Count, dataSet.Tables[0].Columns.Count];

            if (dataSet.Tables[0].Rows.Count > 0)
            {
                for (int j = 0; j < dataSet.Tables[0].Columns.Count; j++)
                    o[0, j] = dataSet.Tables[0].Columns[j].ColumnName;

                for (int rowNo = 1; rowNo < dataSet.Tables[0].Rows.Count; rowNo++)
                    for (int colNo = 0; colNo < dataSet.Tables[0].Columns.Count; colNo++)
                        o[rowNo, colNo] = dataSet.Tables[0].Rows[rowNo][colNo].ToString();
            }
            CurrentWorkSheet++;
            workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
            workSheets.Name = WorkSheetName;
            range = workSheets.UsedRange;
            range = range.get_Resize(dataSet.Tables[0].Rows.Count, dataSet.Tables[0].Columns.Count);
            range.Value2 = o;
        }
        public void Add(List<List<string>> l)
        {
            var colNo = 0;
            var rowNo = 0;
            var o = new Object[l.Count, l[0].Count];

            foreach (var listofstring in l)
            {
                foreach (string s in listofstring)
                {
                    o[rowNo, colNo] = s;
                    colNo++;
                }
                colNo = 0;
                rowNo++;
            }
            CurrentWorkSheet++;
            workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
            workSheets.Name = WorkSheetName;
            range = workSheets.UsedRange;
            range = range.get_Resize(l.Count, l[0].Count);
            range.Value2 = o;
        }

        public void Add(Dictionary<Guid, Order> orders)
        {
            if(orders.Count > 0)
            {
                int rows = 2;
                CurrentWorkSheet++;
                workSheets = (Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, CurrentWorkSheet, XlSheetType.xlWorksheet);
                if (workSheets != null)
                {
                    workSheets.Cells[1, 1] = "Date";
                    workSheets.Cells[1, 2] = "Name";
                    workSheets.Cells[1, 3] = "Side";
                    workSheets.Cells[1, 4] = "Target Qty";
                    workSheets.Cells[1, 5] = "Target Px";
                    workSheets.Cells[1, 6] = "Style";
                    workSheets.Cells[1, 7] = "Tif";
                    workSheets.Cells[1, 8] = "Executed Avg Px";
                    workSheets.Cells[1, 9] = "Total Executed Qty";
                    workSheets.Cells[1, 10] = "Executed Avg Px";
                    workSheets.Cells[1, 10] = "Status";

                    foreach (var o in orders.Values)
                    {
                        workSheets.Cells[rows, 1] = o.SendTime.ToOADate();
                        workSheets.Cells[rows, 2] = o.StaticData.Name;
                        workSheets.Cells[rows, 3] = o.Side.ToString();
                        workSheets.Cells[rows, 4] = o.TargetQuantity;
                        workSheets.Cells[rows, 5] = o.TargetPrice;
                        workSheets.Cells[rows, 6] = o.Style.ToString();
                        workSheets.Cells[rows, 7] = o.Ext.Tif.ToString();
                        workSheets.Cells[rows, 8] = o.ExecutedAveragePrice;
                        workSheets.Cells[rows, 9] = o.TotalExecutedQuantity;
                        workSheets.Cells[rows, 10] = o.ExecutedAveragePrice;
                        workSheets.Cells[rows, 10] = o.Status.ToString();
                        rows++;
                    }
                }

            }
        }
        public void Flush()
        {
            try
            {
                workbook.SaveAs(GlobalVariables.REPORTS + "\\" + FileName, XlFileFormat.xlWorkbookNormal, null, Type.Missing, false,
                                Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, true, null, Type.Missing, Type.Missing);

            }
            catch (COMException e)
            {
                
            }
        }
        public void Close()
        {
            workbook.Close(true, Type.Missing, Type.Missing);
            //workbook.Close(true, Type.Missing, Type.Missing);
            app.Quit();
            Dispose();
        }

        #region IDisposable Members

        public void Dispose()
        {
            GC.GetTotalMemory(true);
            Marshal.ReleaseComObject(range);
            Marshal.ReleaseComObject(workSheets);
            Marshal.ReleaseComObject(workbooks);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(app);
            GC.WaitForPendingFinalizers();
            GC.GetTotalMemory(false);

            range = null;
            workSheets = null;
            workbooks = null;
            workbook = null;
            app = null;
        }

        #endregion

    }
}
